Aiven for PostgreSQL

Aiven for PostgreSQL agent setup

DBtune offers a database monitoring agent that operates within your network, securely transmitting metric data to DBtune and applying new proposed configurations. Additionally, the agent is available as a ready-to-use Docker image hosted on public repositories.

Required permissions

Before starting the DBtune agent, you need to make sure that the following prerequisites are met, as documented on this page.

Setting up the agent

There are multiple ways to run the DBtune agent, below is an example using Docker container:

docker run --restart always --detach \
    -e DBT_POSTGRESQL_CONNECTION_URL="postgresql://..." \
    -e DBT_AIVEN_API_TOKEN=your-api-token \
    -e DBT_AIVEN_PROJECT_NAME=your-project-name \
    -e DBT_AIVEN_SERVICE_NAME=your-service-name \
    -e DBT_DBTUNE_SERVER_URL=https://app.dbtune.com \
    -e DBT_DBTUNE_API_KEY=your-dbtune-api-key \
    -e DBT_DBTUNE_DATABASE_ID=your-dbtune-database-id \
    public.ecr.aws/dbtune/dbtune/agent:latest --aiven

You will get the relevant DBT_DBTUNE variables inside the agent tab when setting up your database.

Visit the DBtune agent repository for more information.

Environment variables

Variable	Description
DBT_POSTGRESQL_CONNECTION_URL	Connection URL to the Aiven PostgreSQL database.
DBT_AIVEN_API_TOKEN	The token to call Aiven’s API for your account.
DBT_AIVEN_PROJECT_NAME	Name of the project within your service is running.
DBT_AIVEN_SERVICE_NAME	Name of your Aiven PostgreSQL service.
DBT_DBTUNE_SERVER_URL	URL of the DBtune server.
DBT_DBTUNE_API_KEY	Your DBtune API key for authentication.
DBT_DBTUNE_DATABASE_ID	Database ID assigned by DBtune for this database instance.

Parameters tuned

The parameters tuned by DBtune change with respect to the chosen tuning mode. Below are the two tuning modes supported.

More paramteters will be tuned by DBtune when Aiven will open them through their APIs. Check this page regularly.

Reload-only tuning mode

This tuning mode tunes without restarting the database server and using only reloads. This does not cause any downtime and is suitable for critical production environments. The following parameters are tuned in this mode:

1. work_mem
2. bgwriter_delay
3. bgwriter_lru_maxpages
4. max_parallel_workers
5. max_parallel_workers_per_gather

Restart tuning mode

Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can choose this mode which restarts the database up to 30 times in a span of few hours; The following parameter is tuned in this mode in addition to the parameters tuned in the reload-only mode:

6. shared_buffers

We use the Aiven API to modify parameters, which limits the set of available parameters that we would normally tune for restart tuning mode.

Optimization objectives

The recommended objective to use is average query runtime (AQR), which DBtune will minimize as it suggests configurations. For additional metrics, please refer to our section on choosing an alternative optimization objectives.

Agent monitoring stats

info

DBtune only retrieves performance metrics from the database and does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.

Below is the data DBtune collects and sends to the DBtune server every second. It gathers these metrics by reading from system tables, as well as calling the Aiven API.

Collected data

Category	Metrics
Database Performance	Query Runtime, Transactions Per Second, Active Connections, Cache Hit Ratio, Wait Events, Autovacuum Count
System	Database Size, Server Uptime
Hardware	CPU Utilization, Memory Usage, Freeable Memory

DBtune queries system metrics to ensure production safety. This takes place in 30 second intervals, as Aiven’s API does not provide any finer resolution than this. For up-to-date details, you can refer to the open-source agent.